#!/usr/bin/perl -w
# a quick & dirty converter, not general purpose but works with my db

$filename = "mysql.schema";
open(IN,"<$filename") || die("Can't open input file $filename!\n");
open(OUT,">sqlite.schema") || die("Can't open output file!\n");

$stage=0;
while(<IN>){
	chop;
	s/^\s+//;
	s/`//g;
	if(/^CREATE TABLE /){
		($table) = split(/\(/,$');
		start_table();
		$stage=1;
	}
	elsif(($stage==1) && /^\) /){
		end_table();
		$stage=0;
	}
	elsif($stage){
		@fields = split(/[ ,]+/);
		do_fields();
	}
}

sub start_table {
	
	($table) = split(/ /,$table);
	$index_str = '';
	$primary = '';
	@table = ();
}

sub do_fields {

	$unique = '';
	$name = shift(@fields);

	if($name eq "UNIQUE"){ 
		$unique='unique'; 
		$name = shift(@fields); 
	}
	elsif($name eq "PRIMARY"){ 
		shift(@fields);
		$primary = shift(@fields);
		$primary =~ s/[\(\)]+//g;
		return;
	}
	if($name eq "KEY"){
		$key = shift(@fields);
		$index_str .= "create $unique"." index $table".'_'."$key on $table (";
		foreach $field (@fields){
			$field =~ s/[, \(\)]+//g;
			if($field ne ''){
				$index_str .= $field.',';
			}
		}
		$index_str =~ s/,$//;
		$index_str .= ");\n";
		return;
	}

	$type = shift(@fields);
	if($type =~ /char/){ 
		#no change
	}
	elsif($type =~ /bigint/){
		$type = "bigint";		#remove (20)
	}
	elsif($type =~ /tinyint/ || $type =~ /int/){
		$type = "integer";
	}
	elsif($type =~ /date/){
		$type = "char (12)";
	}
	else{
		die("Unknown type $type!\n");
	}

	$var_str = "$name $type";
	$default = shift(@fields);

	if($default eq "unsigned"){
		$var_str .= ' unsigned';
		$default = shift(@fields);		
	}
	if($default eq 'NOT'){
		$default = shift(@fields);
		$default = shift(@fields);
		if(defined($default) && $default eq 'auto_increment'){
			$default = 'autoincrement';
		}
		else {
			$default = '';
		}
	}
	else{
		$default .= ' '.shift(@fields);
	}
	$var_str .= ' '.$default;
	push(@table,$var_str);

}

sub end_table {

	$table_str = "create table $table (";
	foreach $var (@table){
		if($primary ne '' && $var =~ /^$primary/){
			if($var =~ /unsigned/){
				$var =~ s/unsigned//;
			}
			if($var =~ /autoincrement/){
				$var =~ s/autoincrement/primary key autoincrement/;
			}
			else{
				$var .= ' primary key';
			}
		}
		$table_str .= $var.', ';
	}

	$table_str =~ s/, $//;
	$table_str =~ s/ {2,}/ /g; #remove extra spaces
	$table_str .= ");\n";

	print OUT "$table_str";
	add_indexes();

}

sub add_indexes {

	$index_str =~ s/ {2,}/ /g;
	print OUT $index_str;
}
